// MARK 社保管理
require('dotenv').config()
// 导入数据库操作模块
const db = require('../../db')

// TODO 创建新用户时增加创建社保模板记录
// MARK POST / social_securitys / list	查询企业社保列表
exports.getList = (req, res) => {

  const { page, pageSize, socialSecurityChecks, providentFundChecks, departmentChecks } = req.body

  // 验证输入数据
  if (!page || !pageSize) {
    return res.status(400).json({ success: false, code: 10001, message: '页码和每页大小不能为空' })
  }

  // 构建查询条件
  let conditions = []
  let values = []

  if (socialSecurityChecks && socialSecurityChecks.length > 0) {
    const ids = socialSecurityChecks.map(check => check.id)
    conditions.push(`participatingInTheCityId IN (${ids.map(() => '?').join(', ')})`)
    values.push(...ids)
  }

  if (providentFundChecks && providentFundChecks.length > 0) {
    const ids = providentFundChecks.map(check => check.id)
    conditions.push(`providentFundCityId IN (${ids.map(() => '?').join(', ')})`)
    values.push(...ids)
  }

  // 处理 departmentChecks
  if (departmentChecks && departmentChecks.length > 0) {
    // 首先查询 employee 表中的 userId，这些 userId 对应的 departmentId 在 departmentChecks 中
    const departmentIds = departmentChecks
    const employeeQueryForDepartment = `
      SELECT 
        id AS userId
      FROM employee
      WHERE departmentId IN (${departmentIds.map(() => '?').join(', ')})
    `
    db.query(employeeQueryForDepartment, departmentIds, (err, employeeResults) => {
      if (err) {
        return res.status(500).json({ success: false, code: 10002, message: '查询员工信息失败', error: err.message })
      }

      const userIdsFromDepartment = employeeResults.map(employee => employee.userId)
      if (userIdsFromDepartment.length === 0) {
        // 如果没有 userIds，直接返回空数组
        return res.json({
          success: true,
          code: 10000,
          data: {
            total: 0,
            rows: []
          },
          message: '执行成功'
        })
      }

      // 将 userIds 添加到条件中
      conditions.push(`userId IN (${userIdsFromDepartment.map(() => '?').join(', ')})`)
      values.push(...userIdsFromDepartment)

      // 构建查询语句
      const query = `
        SELECT 
          participatingInTheCityId,
          providentFundCityId,
          userId,
          providentFundBase,
          socialSecurityBase
        FROM social_securitys
        ${conditions.length > 0 ? `WHERE ${conditions.join(' AND ')}` : ''}
        LIMIT ? OFFSET ?
      `
      const offset = (page - 1) * pageSize
      values.push(pageSize, offset)

      db.query(query, values, (err, results) => {
        if (err) {
          return res.status(500).json({ success: false, code: 10002, message: '查询社保列表失败', error: err.message })
        }

        // 查询 total 记录数
        const totalQuery = `
          SELECT COUNT(*) AS total 
          FROM social_securitys
          ${conditions.length > 0 ? `WHERE ${conditions.join(' AND ')}` : ''}
        `
        db.query(totalQuery, values.slice(0, -2), (err, totalResult) => {
          if (err) {
            return res.status(500).json({ success: false, code: 10002, message: '查询社保总数失败', error: err.message })
          }

          const total = totalResult[0].total

          // 批量查询员工信息
          const userIds = results.map(row => row.userId)
          if (userIds.length === 0) {
            // 如果没有 userIds，直接返回空数组
            return res.json({
              success: true,
              code: 10000,
              data: {
                total,
                rows: []
              },
              message: '执行成功'
            })
          }

          const employeeQuery = `
            SELECT 
              id,
              mobile,
              username,
              workNumber,
              timeOfEntry,
              leaveTime,
              departmentId
            FROM employee
            WHERE id IN (${userIds.map(() => '?').join(', ')})
          `
          db.query(employeeQuery, userIds, (err, employeeResults) => {
            if (err) {
              return res.status(500).json({ success: false, code: 10002, message: '查询员工信息失败', error: err.message })
            }

            const employeeMap = employeeResults.reduce((acc, employee) => {
              acc[employee.id] = employee
              return acc
            }, {})

            // 获取所有部门ID
            const departmentIdsFromEmployee = Array.from(new Set(employeeResults.map(employee => employee.departmentId)))
            if (departmentIdsFromEmployee.length === 0) {
              // 如果没有 departmentIds，直接返回空数组
              return res.json({
                success: true,
                code: 10000,
                data: {
                  total,
                  rows: []
                },
                message: '执行成功'
              })
            }

            const departmentQuery = `
              SELECT 
                id,
                name AS departmentName
              FROM department
              WHERE id IN (${departmentIdsFromEmployee.map(() => '?').join(', ')})
            `
            db.query(departmentQuery, departmentIdsFromEmployee, (err, departmentResults) => {
              if (err) {
                return res.status(500).json({ success: false, code: 10002, message: '查询部门信息失败', error: err.message })
              }

              const departmentMap = departmentResults.reduce((acc, department) => {
                acc[department.id] = department
                return acc
              }, {})

              // 批量查询城市信息
              const cityIds = Array.from(new Set(results.flatMap(row => [row.participatingInTheCityId, row.providentFundCityId])))
              if (cityIds.length === 0) {
                // 如果没有 cityIds，直接返回空数组
                return res.json({
                  success: true,
                  code: 10000,
                  data: {
                    total,
                    rows: []
                  },
                  message: '执行成功'
                })
              }

              const cityQuery = `
                SELECT 
                  id,
                  name
                FROM city
                WHERE id IN (${cityIds.map(() => '?').join(', ')})
              `
              db.query(cityQuery, cityIds, (err, cityResults) => {
                if (err) {
                  return res.status(500).json({ success: false, code: 10002, message: '查询城市信息失败', error: err.message })
                }

                const cityMap = cityResults.reduce((acc, city) => {
                  acc[city.id] = city.name
                  return acc
                }, {})

                // 处理 results 数据
                const processedRows = results.map(row => {
                  const employee = employeeMap[row.userId]
                  const department = departmentMap[employee ? employee.departmentId : null]
                  const participatingInTheCity = cityMap[row.participatingInTheCityId]
                  const providentFundCity = cityMap[row.providentFundCityId]

                  return {
                    departmentName: department ? department.departmentName : '',
                    id: row.id,
                    mobile: employee ? employee.mobile : '',
                    participatingInTheCity: participatingInTheCity || '',
                    participatingInTheCityId: row.participatingInTheCityId,
                    providentFundBase: row.providentFundBase,
                    providentFundCity: providentFundCity || '',
                    providentFundCityId: row.providentFundCityId,
                    socialSecurityBase: row.socialSecurityBase,
                    leaveTime: employee ? employee.leaveTime : null,
                    timeOfEntry: employee ? employee.timeOfEntry : null,
                    username: employee ? employee.username : '',
                    workNumber: employee ? employee.workNumber : ''
                  }
                })

                // 直接返回数据
                res.json({
                  success: true,
                  code: 10000,
                  data: {
                    total,
                    rows: processedRows
                  },
                  message: '执行成功'
                })
              })
            })
          })
        })
      })
    })
  } else {
    // 如果没有 departmentChecks，则直接查询 social_securitys 表
    // 构建查询语句
    const query = `
      SELECT 
        participatingInTheCityId,
        providentFundCityId,
        userId,
        providentFundBase,
        socialSecurityBase
      FROM social_securitys
      ${conditions.length > 0 ? `WHERE ${conditions.join(' AND ')}` : ''}
      LIMIT ? OFFSET ?
    `
    const offset = (page - 1) * pageSize
    values.push(pageSize, offset)

    db.query(query, values, (err, results) => {
      if (err) {
        return res.status(500).json({ success: false, code: 10002, message: '查询社保列表失败', error: err.message })
      }

      // 查询 total 记录数
      const totalQuery = `
        SELECT COUNT(*) AS total 
        FROM social_securitys
        ${conditions.length > 0 ? `WHERE ${conditions.join(' AND ')}` : ''}
      `
      db.query(totalQuery, values.slice(0, -2), (err, totalResult) => {
        if (err) {
          return res.status(500).json({ success: false, code: 10002, message: '查询社保总数失败', error: err.message })
        }

        const total = totalResult[0].total

        // 批量查询员工信息
        const userIds = results.map(row => row.userId)
        if (userIds.length === 0) {
          // 如果没有 userIds，直接返回空数组
          return res.json({
            success: true,
            code: 10000,
            data: {
              total,
              rows: []
            },
            message: '执行成功'
          })
        }

        const employeeQuery = `
          SELECT 
            id,
            mobile,
            username,
            workNumber,
            timeOfEntry,
            leaveTime,
            departmentId
          FROM employee
          WHERE id IN (${userIds.map(() => '?').join(', ')})
        `
        db.query(employeeQuery, userIds, (err, employeeResults) => {
          if (err) {
            return res.status(500).json({ success: false, code: 10002, message: '查询员工信息失败', error: err.message })
          }

          const employeeMap = employeeResults.reduce((acc, employee) => {
            acc[employee.id] = employee
            return acc
          }, {})

          // 获取所有部门ID
          const departmentIds = Array.from(new Set(employeeResults.map(employee => employee.departmentId)))
          if (departmentIds.length === 0) {
            // 如果没有 departmentIds，直接返回空数组
            return res.json({
              success: true,
              code: 10000,
              data: {
                total,
                rows: []
              },
              message: '执行成功'
            })
          }

          const departmentQuery = `
            SELECT 
              id,
              name AS departmentName
            FROM department
            WHERE id IN (${departmentIds.map(() => '?').join(', ')})
          `
          db.query(departmentQuery, departmentIds, (err, departmentResults) => {
            if (err) {
              return res.status(500).json({ success: false, code: 10002, message: '查询部门信息失败', error: err.message })
            }

            const departmentMap = departmentResults.reduce((acc, department) => {
              acc[department.id] = department
              return acc
            }, {})

            // 批量查询城市信息
            const cityIds = Array.from(new Set(results.flatMap(row => [row.participatingInTheCityId, row.providentFundCityId])))
            if (cityIds.length === 0) {
              // 如果没有 cityIds，直接返回空数组
              return res.json({
                success: true,
                code: 10000,
                data: {
                  total,
                  rows: []
                },
                message: '执行成功'
              })
            }

            const cityQuery = `
              SELECT 
                id,
                name
              FROM city
              WHERE id IN (${cityIds.map(() => '?').join(', ')})
            `
            db.query(cityQuery, cityIds, (err, cityResults) => {
              if (err) {
                return res.status(500).json({ success: false, code: 10002, message: '查询城市信息失败', error: err.message })
              }

              const cityMap = cityResults.reduce((acc, city) => {
                acc[city.id] = city.name
                return acc
              }, {})

              // 处理 results 数据
              const processedRows = results.map(row => {
                const employee = employeeMap[row.userId]
                const department = departmentMap[employee ? employee.departmentId : null]
                const participatingInTheCity = cityMap[row.participatingInTheCityId]
                const providentFundCity = cityMap[row.providentFundCityId]

                return {
                  departmentName: department ? department.departmentName : '',
                  id: row.id,
                  mobile: employee ? employee.mobile : '',
                  participatingInTheCity: participatingInTheCity || '',
                  participatingInTheCityId: row.participatingInTheCityId,
                  providentFundBase: row.providentFundBase,
                  providentFundCity: providentFundCity || '',
                  providentFundCityId: row.providentFundCityId,
                  socialSecurityBase: row.socialSecurityBase,
                  leaveTime: employee ? employee.leaveTime : null,
                  timeOfEntry: employee ? employee.timeOfEntry : null,
                  username: employee ? employee.username : '',
                  workNumber: employee ? employee.workNumber : ''
                }
              })

              // 直接返回数据
              res.json({
                success: true,
                code: 10000,
                data: {
                  total,
                  rows: processedRows
                },
                message: '执行成功'
              })
            })
          })
        })
      })
    })
  }
}
// MARK GET / social_securitys / settings	查询企业社保配置
// MARK GET / social_securitys / { id }	查询用户社保数据
// MARK PUT / social_securitys / { id }	保存 / 更新用户社保数据
// MARK GET / social_securitys / payment_item / { id }	查询参保项目


/**
 * 根据page与pageSize查询social_securitys表对应条数的{
 *  departmentId
 *  participatingInTheCityId
 * providentFundCityId
 *  userId
 * providentFundBase
 * socialSecurityBase
 * }
 * 最终返回格式如下(这里为了演示格式所以rows只有一份数据，实际应该结合pageSize查找)
 {
  "success": true,
  "code": 10000,
  "data": {
      "total": 28,
      "rows": [
          {
              "departmentName": "总裁办", // 根据departmentId 查询department表中对应的name字段值
              "id": 1,
              "mobile": "13800000002",
              "participatingInTheCity": "深圳", // 根据participatingInTheCityId查询city表对应的name字段值
              "participatingInTheCityId": 3,
              "providentFundBase": 19190,
              "providentFundCity": "深圳",//根据providentFundCityId 查询city表对应的name字段值
              "providentFundCityId": 3,
              "socialSecurityBase": 7905,
              "leaveTime": null,// 根据userId查询employee表中对应的leaveTime字段值
              "timeOfEntry": "2022-10-24",// 根据userId查询employee表中对应的timeOfEntry字段值
              "username": "管理员", // 根据userId查询employee表中对应的name字段值
              "workNumber": "HEIMA00001" // 根据userId查询employee表中对应的workNumber字段值
          }
      ]
  },
  "message": "执行成功"
}
 */